﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;

namespace WebApplication1
{
    /// <summary>
    /// Excel文件扩展名
    /// </summary>
    public enum ExcelExtension
    {
        XLSX,
        XLS
    }

    /// <summary>
    /// Npoi操作Excel类
    /// </summary>
    public class NpoiExcelHelper
    {
        /// <summary>
        /// 获取Excel文件的后缀
        /// </summary>
        /// <param name="fileName">文件路径/文件名称（含后缀名）</param>
        /// <returns></returns>
        private static ExcelExtension GetExcelExtension(string fileName)
        {
            string extension = Path.GetExtension(fileName);
            switch (extension.ToLowerInvariant())
            {
                case ".xlsx":
                    return ExcelExtension.XLSX;
                case ".xls":
                default:
                    return ExcelExtension.XLS;
            }
        }

        /// <summary>
        /// 判断Excel文件的后缀是否xlsx
        /// </summary>
        /// <param name="fileName">文件路径/文件名称（含后缀名）</param>
        /// <returns></returns>
        private static bool GetExcelIsXlsx(string fileName)
        {
            return GetExcelExtension(fileName) == ExcelExtension.XLSX;
        }

        /// <summary>
        /// 根据Excel文件类型返回IWorkbook
        /// </summary>
        /// <param name="fileName">文件路径/文件名称（含后缀名）</param>
        /// <param name="rowNum">Excel行数</param>
        /// <param name="colNum">Excel列数</param>
        /// <returns></returns>
        private static IWorkbook GetWorkbook(string fileName, out int rowNum, out int colNum)
        {
            /*
            Excel 97到Excel 2003，最大行数为65,536行（2^16）。然而，随着Excel的更新和更高版本的发布，行数限制得到了扩展。
            自Excel 2007起，Excel的行数限制提高到了1048576行（2^20），这个版本被称为Excel 2007以及更高版本，包括Excel 2010、Excel 2013、Excel 2016和最新版本的Excel。
            早期版本的Excel中，最大列数为256列（2^8）。然而，随着Excel的更新和更高版本的发布，列数的限制也得到了扩展。
            自Excel 2007起，Excel的最大列数提高到了16384列（2^14），这个版本被称为Excel 2007以及更高版本，包括Excel 2010、Excel 2013、Excel 2016和最新版本的Excel。
            */

            bool isXlsx = GetExcelIsXlsx(fileName);

            if (isXlsx)
            {
                rowNum = 1048576;
                colNum = 16384;
            }
            else
            {
                rowNum = 65536;
                colNum = 256;
            }
            
            if (File.Exists(fileName))
            {
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    if (isXlsx)
                    {
                        return new XSSFWorkbook(fs);
                    }
                    else
                    {
                        return new HSSFWorkbook(fs);
                    }
                }
            }
            else
            {
                if (isXlsx)
                {
                    return new XSSFWorkbook();
                }
                else
                {
                    return new HSSFWorkbook();
                }
            }
        }

        /// <summary>
        /// 将DataTable中的数据导入到excel中
        /// 支持根据DataTable数据及Excel自动分页（多个Sheet）
        /// </summary>
        /// <param name="fileName">文件名称（含后缀名）</param>
        /// <param name="headers">表头</param>
        /// <param name="data">数据DataTable（只包含需要导出的数据的列）</param>
        /// <param name="sheetName">Excel中Sheet名称（多个sheet时 名字后面自动加上数字序号）</param>
        /// <param name="sheetPageSize">每个sheet显示多少条数据，默认为0时或者超出时会自动计算</param>
        /// <param name="StartRow">表起始行，索引0开始</param>
        /// <param name="StartCol">表起始列，索引0开始</param>
        /// <returns></returns>
        public static byte[] DataTableToExcel(string fileName, List<ExcelHead> headers, DataTable data, string sheetName = "Sheet", int sheetPageSize = 0, int StartRow = 0, int StartCol = 0)
        {
            byte[] bytes = new byte[0];
            try
            {
                int rowNumExcel = 0;
                int colNumExcel = 0;

                IWorkbook workbook = GetWorkbook(fileName, out rowNumExcel, out colNumExcel);

                //在这里先定义好单元格样式  防止报错 The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
                ICellStyle headerStyle = GetHeaderStyle(workbook);
                ICellStyle dataStyleDefaut = GetDataStyle(workbook);

                IDataFormat dataformat = workbook.CreateDataFormat();

                ICellStyle dataStyleText = GetDataStyle(workbook);
                dataStyleText.DataFormat = dataformat.GetFormat("text");

                ICellStyle dataStyleDateTime = GetDataStyle(workbook);
                dataStyleDateTime.DataFormat = dataformat.GetFormat("yyyy-MM-dd hh:mm:ss");

                if (headers.Count > 0)
                {
                    int maxCol = headers.Max(item => item.LastCol) + 1;
                    int maxRow = headers.Max(item => item.LastRow) + 1;

                    // 筛选出名字不为空的用户
                    List<string> fieldList = new List<string>();
                    foreach (var head in headers)
                    {
                        if (!string.IsNullOrEmpty(head.Field))
                        {
                            if (!fieldList.Contains(head.Field))
                            {
                                fieldList.Add(head.Field);
                            }
                        }
                    }

                    //计算Excel的sheet除去表头的行，还有多少行数据
                    int rowNumAll = rowNumExcel - maxRow;

                    //计算每个sheet要显示多少条数据
                    int rowNum = rowNumAll;
                    if (sheetPageSize > 0)
                    {
                        if (sheetPageSize < rowNumAll)
                        {
                            rowNum = sheetPageSize;
                        }
                    }
                    //数据总记录数
                    var recordNum = data.Rows.Count;
                    //计算多个页数据
                    int totalPage = recordNum % rowNum == 0 ? recordNum / rowNum : recordNum / rowNum + 1;

                    //计算表格的起始行
                    var startRowNum = 0;
                    if (StartRow > 0)
                    {
                        //起始行数 + 表头的行数 + 数据的行数  不能超过 Excel的行数
                        if (((StartRow + 1) + maxRow + rowNum) < rowNumExcel)
                        {
                            startRowNum = StartRow + 1;
                        }
                    }
                    //计算表格的起始列
                    var startColNum = 0;
                    if (StartCol > 0)
                    {
                        //起始列数 + 数据的列数  不能超过 Excel的列数
                        if (((StartCol + 1) + maxCol) < colNumExcel)
                        {
                            startColNum = StartCol + 1;
                        }
                    }

                    for (var i = 0; i < totalPage; i++)
                    {
                        string sheetNameNew = sheetName;
                        if (totalPage > 1)
                        {
                            sheetNameNew = sheetName + (i + 1).ToString();
                        }

                        ISheet sheet = workbook.CreateSheet(sheetNameNew);//创建工作表

                        #region 表头
                        foreach (var item in headers)
                        {
                            IRow headerRow = sheet.GetRow(item.FirstRow + startRowNum);
                            if (headerRow == null)
                            {
                                headerRow = sheet.CreateRow(item.FirstRow + startRowNum);
                                //行高，避免自动换行的内容将行高撑开
                                headerRow.HeightInPoints = 24;
                            }
                            ICell headerCell = headerRow.CreateCell(item.FirstCol + startColNum);
                            headerCell.SetCellValue(item.Name);
                            //设置跨行
                            if (item.FirstRow != item.LastRow || item.LastCol != item.FirstCol)
                            {
                                //CellRangeAddress(开始行,结束行,开始列,结束列)
                                //行列索引由0开始
                                var region = new CellRangeAddress(item.FirstRow + startRowNum, item.LastRow + startRowNum, item.FirstCol + startColNum, item.LastCol + startColNum);
                                sheet.AddMergedRegion(region);
                            }
                            headerCell.CellStyle = headerStyle;
                        }
                        #endregion

                        #region 计算分页数据的开始和结束索引
                        int startIndex = i * rowNum;
                        int endindex = (i + 1) * rowNum - 1;
                        if (endindex >= recordNum)
                        {
                            endindex = recordNum - 1;
                        }
                        #endregion

                        #region 表格数据
                        //数据的起始行
                        var startRow = maxRow + startRowNum;
                        foreach (DataRow item in data.Rows)
                        {
                            // 获取当前DataRow的索引
                            int index = item.Table.Rows.IndexOf(item);
                            if (index >= startIndex && index <= endindex)
                            {
                                IRow dataRow = sheet.GetRow(startRow);
                                if (dataRow == null)
                                {
                                    dataRow = sheet.CreateRow(startRow);
                                    //行高，避免自动换行的内容将行高撑开
                                    dataRow.HeightInPoints = 20;
                                }

                                var startCol = StartCol + startColNum;
                                foreach (var field in fieldList)
                                {
                                    ICell dataCell = dataRow.CreateCell(startCol);
                                    dataCell.CellStyle = dataStyleDefaut;

                                    DataColumn column = data.Columns[field];
                                    var dataValue = item[column];
                                    switch (column.DataType.ToString())
                                    {
                                        case "System.String":
                                            dataCell.SetCellValue(dataValue.ToString());
                                            dataCell.CellStyle = dataStyleText;
                                            break;
                                        case "System.DateTime":
                                            DateTime.TryParse(dataValue?.ToString(), out DateTime date);
                                            dataCell.SetCellValue(date);
                                            dataCell.CellStyle = dataStyleDateTime;
                                            break;
                                        case "System.Boolean":
                                            bool.TryParse(dataValue?.ToString(), out bool boolValue);
                                            dataCell.SetCellValue(boolValue);
                                            break;
                                        case "System.Int16":
                                        case "System.Int32":
                                        case "System.Int64":
                                            int.TryParse(dataValue?.ToString(), out int intValue);
                                            dataCell.SetCellValue(intValue);
                                            break;
                                        case "System.Byte":
                                            int.TryParse(dataValue?.ToString(), out int byteValue);
                                            dataCell.SetCellValue(byteValue);
                                            break;
                                        case "System.Decimal":
                                        case "System.Double":
                                            double.TryParse(dataValue?.ToString(), out double doubleValue);
                                            dataCell.SetCellValue(doubleValue);
                                            //精确到小数点后两位
                                            //dataCell.CellStyle.DataFormat = dataformat.GetFormat("0.00");
                                            //添加千分位分割,并保留两位小数
                                            //dataCell.CellStyle.DataFormat = dataformat.GetFormat("#,##0.00");
                                            //金钱格式-千分位分割,并保留两位小数
                                            //dataCell.CellStyle.DataFormat = dataformat.GetFormat("￥#,##0.00");
                                            //中文大写(会有四舍五入的情况)
                                            //dataCell.CellStyle.DataFormat = dataformat.GetFormat("[DbNum2][$-804]0");
                                            //科学计数法
                                            //dataCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                                            break;
                                        case "System.DBNull": //空值
                                            dataCell.SetCellValue("");
                                            break;
                                        default:
                                            dataCell.SetCellValue(dataValue?.ToString());
                                            break;
                                    }
                                    startCol++;
                                }
                                startRow++;
                            }
                        }
                        #endregion
                    }
                }
                #region 输出Excel
                using (MemoryStream stream = new MemoryStream())
                {
                    workbook.Write(stream);
                    bytes = stream.ToArray();
                }
                #endregion
            }
            catch (Exception ex)
            { 
                
            }
            return bytes;
        }

        /// <summary>
        /// 将Excel文件中的数据读出到DataTable
        /// </summary>
        /// <param name="fileName">文件路径（含文件名称和后缀名）</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string fileName, int sheetIndex = 0)
        {
            DataTable dt = new DataTable();

            try
            {
                int rowNumExcel = 0;
                int colNumExcel = 0;

                var isXlsx = GetExcelIsXlsx(fileName);

                IWorkbook workbook = GetWorkbook(fileName, out rowNumExcel, out colNumExcel);

                dt = GetExcelDataTable(workbook);

                // 获取sheet的数量
                int numberOfSheets = workbook.NumberOfSheets;
                for (int index = 0; index < numberOfSheets; index++)
                {
                    ISheet sheet = workbook.GetSheetAt(index);

                    //数据
                    for (int i = 0; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);

                        if (row != null)
                        {
                            DataRow dr = dt.NewRow();
                            bool hasValue = false;
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                ICell cell = row.GetCell(j);
                                dr[j] = GetValueType(cell);
                                #region 增加格式解析
                                if (cell != null)
                                {
                                    switch (cell.CellType)
                                    {
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                            {
                                                dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss:fff");
                                            }
                                            else//其他数字类型
                                            {
                                                dr[j] = cell.NumericCellValue;
                                            }
                                            break;
                                        case CellType.Blank:
                                            dr[j] = string.Empty;
                                            break;
                                        case CellType.Formula:
                                            if (isXlsx)
                                            {
                                                IFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook);
                                                if (eva.Evaluate(cell).CellType == CellType.Numeric)
                                                {
                                                    if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                                    {
                                                        dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss:fff");
                                                    }
                                                    else//其他数字类型
                                                    {
                                                        dr[j] = cell.NumericCellValue;
                                                    }
                                                }
                                                else
                                                {
                                                    dr[j] = eva.Evaluate(cell).StringValue;
                                                }
                                            }
                                            else
                                            {
                                                IFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);
                                                if (eva.Evaluate(cell).CellType == CellType.Numeric)
                                                {
                                                    if (DateUtil.IsCellDateFormatted(cell))//日期类型
                                                    {
                                                        dr[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss:fff");
                                                    }
                                                    else//其他数字类型
                                                    {
                                                        dr[j] = cell.NumericCellValue;
                                                    }
                                                }
                                                else
                                                {
                                                    dr[j] = eva.Evaluate(cell).StringValue;
                                                }
                                            }
                                            break;
                                        default:
                                            dr[j] = cell.StringCellValue;
                                            break;

                                    }
                                }
                                #endregion

                                if (dr[j] != null && dr[j].ToString() != string.Empty)
                                {
                                    hasValue = true;
                                }
                            }
                            if (hasValue)
                            {
                                dt.Rows.Add(dr);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            { 
                
            }
            return dt;
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中(第一行是标题)
        /// 支持多个sheet数据导入（建议多个sheet的数据格式保持一致，将没有数据的sheet删除）
        /// </summary>
        /// <param name="fileName">文件路径（含文件名称后缀名）</param>
        /// <param name="columnFieldText">字段对应中文 顺序需要跟Excel中数据顺序一致</param>
        /// <param name="sheetName">指定Excel中Sheet名称 如果为null时，读取所有sheet中的数据</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(string fileName, string[,] columnFieldText = null, string sheetName = null)
        {
            DataTable data = new DataTable();
            int rowNum = 0;
            int colNum = 0;
            IWorkbook workbook = GetWorkbook(fileName, out rowNum, out colNum);

            for (int e = 0; e < workbook.NumberOfSheets; e++)
            {
                ISheet sheet = workbook.GetSheetAt(e);
                if (sheet != null)
                {
                    var currentSheetIndex = 0;
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        if (sheet.SheetName == sheetName)
                        {
                            currentSheetIndex = e;
                        }
                    }

                    IRow firstRow = sheet.GetRow(0);
                    if (firstRow != null)
                    {
                        int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                        var dataColumn = columnFieldText != null ? columnFieldText.GetLength(0) : cellCount;
                        int startRow = sheet.FirstRowNum;
                        if (dataColumn <= colNum)
                        {
                            if (e == currentSheetIndex)
                            {
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    ICell cell = firstRow.GetCell(i);
                                    if (cell != null)
                                    {
                                        string cellValue = cell.StringCellValue;
                                        if (cellValue != null)
                                        {
                                            DataColumn column = new DataColumn((columnFieldText != null ? columnFieldText[i, 0] : cellValue));
                                            data.Columns.Add(column);
                                        }
                                    }
                                }
                            }

                            startRow = sheet.FirstRowNum + 1;

                            //最后一列的标号
                            int rowCount = sheet.LastRowNum;
                            for (int i = startRow; i <= rowCount; ++i)
                            {
                                IRow row = sheet.GetRow(i);
                                if (row == null) continue; //没有数据的行默认是null　　　　　　　

                                DataRow dataRow = data.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                        dataRow[j] = row.GetCell(j).ToString();
                                }
                                data.Rows.Add(dataRow);
                            }
                        }
                        else
                        {
                            //数据列数超过了Excel的列数
                        }
                    }

                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        if (sheet.SheetName == sheetName)
                        {
                            break;
                        }
                    }
                }
            }
            return data;
        }

        /// <summary>
        /// 表头单元格样式
        /// </summary>
        private static ICellStyle GetHeaderStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            //居中
            style.Alignment = HorizontalAlignment.Center;
            //垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;
            //自动换行 
            style.WrapText = true;
            //边框
            //style.BorderBottom = BorderStyle.Thin;
            //style.BorderLeft = BorderStyle.Thin;
            //style.BorderRight = BorderStyle.Thin;
            //style.BorderTop = BorderStyle.Thin;
            //边框颜色
            //style.TopBorderColor = HSSFColor.Black.Index;
            //style.BottomBorderColor = HSSFColor.Black.Index;
            //style.RightBorderColor = HSSFColor.Black.Index;
            //style.LeftBorderColor = HSSFColor.Black.Index;
            //字体
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 12;
            font.FontName = "宋体";
            font.IsBold = true;
            style.SetFont(font);
            return style;
        }
        /// <summary>
        /// 数据单元格样式
        /// </summary>
        private static ICellStyle GetDataStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            style.WrapText = true;//自动换行
            //边框
            //style.BorderBottom = BorderStyle.Thin;
            //style.BorderLeft = BorderStyle.Thin;
            //style.BorderRight = BorderStyle.Thin;
            //style.BorderTop = BorderStyle.Thin;
            // 字体
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 12;
            font.FontName = "宋体";
            style.SetFont(font);

            return style;
        }

        /// <summary>
        /// 根据单元格将内容返回为对应类型的数据
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>数据</returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        /// <summary>
        /// 获取Excel文件中的数据的DataTable列
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private static DataTable GetExcelDataTable(IWorkbook workbook)
        {
            DataTable dt = new DataTable();

            // 获取sheet的数量
            int numberOfSheets = workbook.NumberOfSheets;
            for (int index = 0; index < numberOfSheets; index++)
            {
                ISheet sheet = workbook.GetSheetAt(index);

                IRow row = sheet.GetRow(sheet.FirstRowNum);
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    var columnName = "Columns" + i.ToString();
                    if (!dt.Columns.Contains(columnName))
                    {
                        dt.Columns.Add(new DataColumn(columnName));
                    }
                }
            }
            return dt;
        }

    }

    /// <summary>
    /// 表头格式
    /// </summary>
    public class ExcelHead
    {
        /// <summary>
        /// 字段名（为空时代表是多表头）
        /// </summary>
        public string Field { set; get; }
        /// <summary>
        /// 表头名称
        /// </summary>
        public string Name { set; get; }
        /// <summary>
        /// 开始行，索引0开始
        /// </summary>
        public int FirstRow { set; get; }
        /// <summary>
        /// 结束行，索引0开始
        /// </summary>
        public int LastRow { set; get; }
        /// <summary>
        /// 开始列，索引0开始
        /// </summary>
        public int FirstCol { set; get; }
        /// <summary>
        /// 结束列，索引0开始
        /// </summary>
        public int LastCol { set; get; }
    }
}